using System.Data;
using System.Data.SqlClient;//同时要添加System.Data.SqlClient包，(Nuget添加)
using System.Reflection;
using Microsoft.Extensions.Configuration;


namespace DBUtil;

public class DbHelper
{
    private static string? _connString;

    // 静态构造方法,
    // 第一次访问 这个的类的时候，会执行一次静态构造


    static DbHelper()
    {
        //ConfigurationBuilder configuration = new Microsoft.Extensions.Configuration.ConfigurationBuilder(); //读取配置文件
        //var config = configuration(Directory.GetCurrentDirectory())
        //    .AddJsonFile(file =>
        //{
        //    file.Path = "/appsettings.json";
        //    file.Optional = false;
        //    file.ReloadOnChange = true;
        //}).Build();
        //_connString = config.GetConnectionString("SqlServer");
    }

    private static SqlCommand PrepareCommand(SqlConnection conn, string sql, CommandType cmdType,
        params SqlParameter[]? parameters)
    {
        using SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.CommandType = cmdType;
        if (parameters != null && parameters.Length > 0)
        {
            cmd.Parameters.AddRange(parameters);
        }

        return cmd;
    }


    /**
     * 执行增删改的操作
     */
    
    public static int ExecuteNonQuery(string sql,params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteNonQuery();
    }
    
    public static int ExecuteNonQuery(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return PrepareCommand(conn, sql, cmdType, parameters).ExecuteNonQuery();
    }

    public static int ExecuteScalar(string sql,params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return Convert.ToInt32(PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteScalar());
    }
    
    public static int ExecuteScalar(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return Convert.ToInt32(PrepareCommand(conn, sql, cmdType, parameters).ExecuteScalar());
    }

    public static List<T> GetList<T>(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)  where T : class, new()
    {
        using SqlDataAdapter adapter = new(PrepareCommand(
            new (_connString),sql, cmdType, parameters));

        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        return ToList<T>(dataTable);
    }
    
    public static List<T> GetList<T>(string sql, params SqlParameter[]? parameters)  where T : class, new()
    {
        using SqlDataAdapter adapter = new(PrepareCommand(
            new (_connString),sql, CommandType.Text, parameters));

        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        return ToList<T>(dataTable);
    }

    private static List<T> ToList<T>(DataTable dt) where T : class, new()
    {
        Type t = typeof(T);
        PropertyInfo[] propertys = t.GetProperties();
        List<T> lst = new List<T>();
        string typeName = string.Empty;

        foreach (DataRow dr in dt.Rows)
        {
            T entity = new T();
            foreach (PropertyInfo pi in propertys)
            {
                typeName = pi.Name;
                if (dt.Columns.Contains(typeName))
                {
                    if (!pi.CanWrite) continue;
                    object value = dr[typeName];
                    if (value == DBNull.Value) continue;
                    if (pi.PropertyType == typeof(string))
                    {
                        pi.SetValue(entity, value.ToString(), null);
                    }
                    else if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?))
                    {
                        pi.SetValue(entity, int.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(DateTime?) || pi.PropertyType == typeof(DateTime))
                    {
                        pi.SetValue(entity, DateTime.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(float))
                    {
                        pi.SetValue(entity, float.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(double))
                    {
                        pi.SetValue(entity, double.Parse(value.ToString()), null);
                    }
                    else
                    {
                        pi.SetValue(entity, value, null);
                    }
                }
            }

            lst.Add(entity);
        }

        return lst;
    }
}